package com.gxuwz.lhleavesys.dao.impl;

import com.gxuwz.lhleavesys.dao.StudentDao;
import com.gxuwz.lhleavesys.db.DbUtil;
import com.gxuwz.lhleavesys.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户数据访问Dao组件
 */
@Repository
public class StudentDaoImpl implements StudentDao {

    @Autowired
    private DbUtil dbUtil;

    public StudentDaoImpl() {
    }

    public StudentDaoImpl(DbUtil dbUtil) {
        this.dbUtil = dbUtil;
    }

    /**
     *
     * @param student
     * @throws SQLException
     */
    public boolean insert(Student student) throws SQLException {
        Connection conn = null;
        // 2:声明发送SQL命令的陈述对象pstmt

        PreparedStatement pstmt = null;
        // 3:声明带占位符？SQL命令的字符串变量sql
        String sql = "insert into sys_student(stuID,classID,name,sex,address,telNumber,contact,contactTel)values(?,?,?,?,?,?,?,?)";
        try {
            // 4：调用dbUtil对象的getConn()创建Connection对象,特别注意：先创建dbUtil对象
            conn = dbUtil.getConn();
            pstmt = conn.prepareStatement(sql);
            // 5：设置2 SQL命令中占位符?对应值
            int paramIndex = 1;
            pstmt.setString(paramIndex++, student.getStuID());
            pstmt.setString(paramIndex++, student.getClassID());
            pstmt.setString(paramIndex++, student.getName());
            pstmt.setString(paramIndex++, student.getSex());
            pstmt.setString(paramIndex++, student.getAddress());
            pstmt.setString(paramIndex++, student.getTelNumber());
            pstmt.setString(paramIndex++, student.getContact());
            pstmt.setString(paramIndex++, student.getContactTel());
            // 6：发送SQL、执行并返回结果
            int c = pstmt.executeUpdate();

        } catch (Exception e) {
            throw new SQLException("执行插入数据操作发生异常：" + e.getMessage(), e);
        } finally {
            // 关闭数据库连接资源相关对象
            dbUtil.close(pstmt, conn);
        }
        return true;
    }

    /**
     * 删除
     * 
     * @param id
     * @throws SQLException
     */
    public boolean delete(String id) throws SQLException {
        Connection conn = null;
        // 2:声明发送SQL命令的陈述对象pstmt
        PreparedStatement pStmt = null;
        // 3:声明带占位符？SQL命令的字符串变量sql
        String sql = "delete from sys_student where stuID=?";
        try {
            // 4：调用dbUtil对象的getConn()创建Connection对象,特别注意：先创建dbUtil对象
            conn = dbUtil.getConn();
            pStmt = conn.prepareStatement(sql);
            // 5：设置2 SQL命令中占位符?对应值
            int paramIndex = 1;
            pStmt.setString(paramIndex++, id);
            // 6：发送SQL、执行并返回结果
            int c = pStmt.executeUpdate();

        } catch (Exception e) {
            throw new SQLException("执行删除数据操作发生异常：" + e.getMessage(), e);
        } finally {
            // 关闭数据库连接资源相关对象
            dbUtil.close(pStmt, conn);
        }
        return true;
    }

    /**
     * 更新
     *
     * @param student
     * @throws SQLException
     */
    public boolean update(Student student, String id) throws SQLException {
        Connection conn = null;
        // 2:声明发送SQL命令的陈述对象pstmt
        PreparedStatement pstmt = null;
        // 3:声明带占位符？SQL命令的字符串变量sql
        String sql = "update sys_student set stuID=?,classID=?,name=?,sex=? ,address=? , telNumber=?, contact=?,contactTel=? where stuID=?";
        try {
            // 4：调用dbUtil对象的getConn()创建Connection对象,特别注意：先创建dbUtil对象
            conn = dbUtil.getConn();
            pstmt = conn.prepareStatement(sql);
            // 5：设置2 SQL命令中占位符?对应值
            int paramIndex = 1;
            pstmt.setString(paramIndex++, student.getStuID());
            pstmt.setString(paramIndex++, student.getClassID());
            pstmt.setString(paramIndex++, student.getName());
            pstmt.setString(paramIndex++, student.getSex());
            pstmt.setString(paramIndex++, student.getAddress());
            pstmt.setString(paramIndex++, student.getTelNumber());
            pstmt.setString(paramIndex++, student.getContact());
            pstmt.setString(paramIndex++, student.getContactTel());
            pstmt.setString(paramIndex, id);
            // 6：发送SQL、执行并返回结果
            int c = pstmt.executeUpdate();

        } catch (Exception e) {
            throw new SQLException("执行更新数据操作发生异常：" + e.getMessage(), e);

        } finally {
            // 关闭数据库连接资源相关对象
            dbUtil.close(pstmt, conn);
        }
        return true;
    }

    public List<Student> query(Student student) throws SQLException {
        System.out.println(dbUtil);
        List<Student> list = new ArrayList<Student>();
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        List<Object> paramsList = new ArrayList<Object>();
        String sql = "select * from sys_student where 1=1";
        try {
            // 添加查询处理过程
            conn = dbUtil.getConn();
            // 设置占位符？对应的参数
            if (student != null && student.getName() != null) {
                sql += " and  name=?";
                paramsList.add(student.getName());
            }
            if (student != null && student.getPassword() != null) {
                sql += " and  password=?";
                paramsList.add(student.getPassword());
            }
            pStmt = conn.prepareStatement(sql);
            int paramIndex = 1;
            for (Object obj : paramsList) {

                if (obj instanceof String) {
                    pStmt.setString(paramIndex++, (String) obj);
                } else if (obj instanceof Integer) {
                    pStmt.setInt(paramIndex++, (Integer) obj);
                }
            }
            // 发送SQL、执行并返回结果集(ResultSet)
            rs = pStmt.executeQuery();
            // 遍历结果集ResultSet
            while (rs.next()) {
                Student student1 = new Student();
                // 从rs中取出每一列对应的值
                student1.setName(rs.getString("name"));
                student1.setPassword(rs.getString("password"));
                // 加入返回的list
                list.add(student1);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("执行查询数据操作发生异常:" + e.getMessage(), e);
        } finally {
            // 关闭资源
            dbUtil.close(rs, pStmt, conn);
        }
    }

    /**
     * 查询单个学生信息
     * 
     * @param name
     * @return
     */
    @Override
    public Student findByName(String name) {
        Student student = null;
        String sql = "SELECT * FROM sys_student WHERE name = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql);) {
            ps.setString(1, name);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    student = new Student();
                    student.setStuID(rs.getString("stuID"));
                    student.setClassID(rs.getString("classID"));
                    student.setName(rs.getString("name"));
                    student.setSex(rs.getString("sex"));
                    student.setAddress(rs.getString("address"));
                    student.setTelNumber(rs.getString("telNumber"));
                    student.setContact(rs.getString("contact"));
                    student.setContactTel(rs.getString("contactTel"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return student;
    }

    @Override
    public Student findStudentBystuID(String id) throws SQLException {
        Student student = null;
        String sql = "SELECT * FROM sys_student WHERE stuID = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    student = new Student();
                    student.setStuID(rs.getString("stuID"));
                    student.setClassID(rs.getString("classID"));
                    student.setName(rs.getString("name"));
                    student.setSex(rs.getString("sex"));
                    student.setAddress(rs.getString("address"));
                    student.setTelNumber(rs.getString("telNumber"));
                    student.setContact(rs.getString("contact"));
                    student.setContactTel(rs.getString("contactTel"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return student;
    }

    @Override
    public Student findstuNoBystuName(String name) throws SQLException {
        Student student = null;
        String sql = "SELECT * FROM sys_student WHERE name = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    student = new Student();
                    student.setStuID(rs.getString("stuID"));
                    student.setClassID(rs.getString("classID"));
                    student.setName(rs.getString("name"));
                    student.setSex(rs.getString("sex"));
                    student.setAddress(rs.getString("address"));
                    student.setTelNumber(rs.getString("telNumber"));
                    student.setContact(rs.getString("contact"));
                    student.setContactTel(rs.getString("contactTel"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return student;
    }

    // 传过来老师的collegeID
    @Override
    public List<Student> getStudentByinstructor(String collegeID) {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT a.stuID, a.classID, a.name, a.sex, a.address, a.telNumber, a.contact, a.contactTel  from sys_student a,sys_classes b ,sys_instructor c where a.classID = b.classID and c.collegeID = ?";

        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, collegeID);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Student student = new Student();
                    student.setStuID(rs.getString("stuID"));
                    student.setClassID(rs.getString("classID"));
                    student.setName(rs.getString("name"));
                    student.setSex(rs.getString("sex"));
                    student.setAddress(rs.getString("address"));
                    student.setTelNumber(rs.getString("telNumber"));
                    student.setContact(rs.getString("contact"));
                    student.setContactTel(rs.getString("contactTel"));
                    students.add(student);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return students;
    }

    public DbUtil getDbUtil() {
        return dbUtil;
    }

    public void setDbUtil(DbUtil dbUtil) {
        this.dbUtil = dbUtil;
    }

    public String toString() {
        return "StudentDao{dbUtil = " + dbUtil + "}";
    }
}
